<?php
$servername = 'localhost';
$username = 'username';
$password = 'password';
$dbname = 'testDB';

// 创建连接
$connect = mysqli_connect($servername, $username, $password);
 
// 检测连接
if (!$connect) {
    die("Connection failed: " . mysqli_connect_error() . "<br />");
}
// 选择数据库
mysqli_select_db($connect, $dbname);
// 设置编码，防止中文乱码
mysqli_query($connect, "set names utf8");

// 查询用户数据并输出
$sql = 'SELECT id, username, password, token, email, phone, collection FROM users ORDER BY id';
$retval = mysqli_query($connect, $sql);
$result['success'] = false;
$result['message'] = '';
if (!$retval) {
    die('ERROR: ' . mysqli_error($connect));
} else {
    // 使用 mysqli_fetch_assoc 获取数据
    $i = 0;
    while ($row = mysqli_fetch_assoc ($retval)) {
        $result['docs'][$i]["id"] = $row['id'];
        $result['docs'][$i]["username"] = $row['username'];
        $result['docs'][$i]["password"] = $row['password'];
        $result['docs'][$i]["token"] = $row['token'];
        $result['docs'][$i]["email"] = $row['email'];
        $result['docs'][$i]["phone"] = $row['phone'];

        // 查询收藏数据
        $sqlCL = 'SELECT name FROM ' . $row['collection'] . ' ORDER BY id';
        $retvalCL = mysqli_query($connect, $sqlCL);
        if (!$retvalCL) {
        die('ERROR: ' . mysqli_error($connect));
        } else {
            while ($rowCL = mysqli_fetch_assoc ($retvalCL)) {
                $result['docs'][$i]['collection'][] = "{$rowCL['name']}";
            }
        }
        $i += 1;
    }
    $result['success'] = true;
    $result['message'] = urlencode('查询成功！');
    // 释放内存
    mysqli_free_result($retval);
    mysqli_free_result($retvalCL);
}

echo urldecode(json_encode($result));
// // 查询用户数据并输出
// $sql = 'SELECT id, username, password, email, phone, collection FROM users ORDER BY id';
// $retval = mysqli_query($connect, $sql);
// if (!$retval) {
//     die('ERROR: ' . mysqli_error($connect));
// } else {
//     echo '<table border="1"><tr><td>ID</td><td>用户</td><td>密码</td><td>邮箱</td><td>手机</td><td>收藏</td></tr>';
//     // 使用 mysqli_fetch_assoc 获取数据
//     while ($row = mysqli_fetch_assoc ($retval)) {
//         // 查询收藏数据
//         $sqlCL = 'SELECT name FROM ' . $row['collection'] . ' ORDER BY id';
//         $retvalCL = mysqli_query($connect, $sqlCL);
//         echo "<tr><td>{$row['id']}</td>" .
//              "<td>{$row['username']}</td>" . 
//              "<td>{$row['password']}</td>" . 
//              "<td>{$row['email']}</td>" .
//              "<td>{$row['phone']}</td>";
//              if (!$retvalCL) {
//                 die('ERROR: ' . mysqli_error($connect));
//              } else {
//                  echo "<td>";
//                  while ($rowCL = mysqli_fetch_assoc ($retvalCL)) {
//                     echo "{$rowCL['name']} ";
//                  }
//                  echo "</td></tr>";
//              }
//     }
//     // 使用 mysqli_fetch
//     echo '</table>' . '<br />';
//     // 释放内存
//     mysqli_free_result($retval);
// }
